package com.LostAndFound.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.LostAndFound.utils.DataSourceUtils;

public class UserHomeDao {
	QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
	String sql = "";
	/* 寻找有效的记录进行展示，包括已经找回的foundFlag=0，未找到的foundFlag=1
	 * 显示时间差在365天内的记录
	 * TIMESTAMPDIFF(DAY, STR_TO_DATE(a.issuetime,'%Y-%m-%d %H:%i:%s'),NOW())<365
	 */
	public List<Map<String, Object>> getAllSearchLoseRecords(String whatFlag) throws SQLException {
		List<Map<String, Object>> articleList = null;
		sql = "select a.*,c.cname from article a,category c "
				+ " where a.cid = c.cid and invalidflag = 1 and whatFlag = ? "
				+ " and TIMESTAMPDIFF(DAY, STR_TO_DATE(a.issuetime,'%Y-%m-%d %H:%i:%s'),NOW())<365 "
				+ " order by issuetime desc ";
		articleList = runner.query(sql, new MapListHandler(), whatFlag);
		return articleList;
	}
	public Map<String, Object> showArticleDetailByAid(String aid) throws SQLException {
		Map<String, Object> article = null;
		sql = "select a.*,c.cname,u.username from article a,category c,user u "
				+ " where a.cid = c.cid and a.uid = u.uid and aid = ?";
		article = runner.query(sql, new MapHandler(), aid);
		return article;
	}
	/* 搜索
	 * 包括已找到的，未找到的，title或cname包含搜素内容的
	 */
	public List<Map<String, Object>> getMySearchRecord(String whatFlag, String searchContent) throws SQLException {
		List<Map<String, Object>> articleList = null;
		sql = "select a.*,c.cname from article a,category c "
				+ " where a.cid = c.cid and whatFlag = ? and invalidflag = 1 and foundflag!=2 and title like ? "
				+ " or whatFlag = ? and invalidflag = 1 and a.cid = c.cid and foundflag!=2 and c.cname like ? "
				+ " order by issuetime desc";
		articleList = runner.query(sql, new MapListHandler(), whatFlag,"%"+searchContent+"%",whatFlag,"%"+searchContent+"%");
		return articleList;
	}
	/*
	 * 弃用
	 */
	public void handleUpdateArticleForConfirmTaken(String aid) throws SQLException {
		sql = "update article set foundflag = 2 where aid = ?";
		runner.update(sql, aid);
	}
}
